package viv.karaoke.book;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

public class SongDataSource {

  // Database fields
  private SQLiteDatabase database;
  private mySQLiteHelper dbHelper;
  private Integer pageSize = 50;
  private long userId;
  
  private String[] songColumns = {		  						 
		  						 mySQLiteHelper.SONG_ID,
		  						 mySQLiteHelper.SONG_NAME, 
		  						 mySQLiteHelper.SONG_ARTIST, 
		  						 mySQLiteHelper.SONG_FIRST_SENTENCE, 
		  						 mySQLiteHelper.SONG_LYRIC,
		  						 mySQLiteHelper.FAVOURITE_USER_ID,
		  						 mySQLiteHelper.FAVOURITE_ID
		  						 };

  public SongDataSource(Context context, long userId) {
    dbHelper = new mySQLiteHelper(context);
    
    this.userId = userId;
  }

  public void open() throws SQLException {
    database = dbHelper.getWritableDatabase();
  }

  public void close() {
    dbHelper.close();
  }

  public song createSong(String songName, String artist, String firstSentence, String lyric) {
    ContentValues values = new ContentValues();
    values.put(mySQLiteHelper.SONG_NAME, songName);
    values.put(mySQLiteHelper.SONG_ARTIST, artist);
    values.put(mySQLiteHelper.SONG_FIRST_SENTENCE, firstSentence);
    values.put(mySQLiteHelper.SONG_LYRIC, lyric);
    
    long insertId = database.insert(mySQLiteHelper.TABLE_SONG, null,values);
    
    Cursor cursor = database.query(mySQLiteHelper.TABLE_SONG, songColumns, mySQLiteHelper.SONG_ID + " = " + insertId, null, null, null, null);

    song newSong = new song();
	try{
	    cursor.moveToFirst();
	    newSong = cursorToSong(cursor);
	}
	catch(Exception ex)
	{
	}
    finally
    {
    	//Make sure to close the cursor
    	cursor.close();
    }
    
    return newSong;
  }

  public void deleteSong(song song) {
    long id = song.getSongId();
    System.out.println("Song deleted with id: " + id);
    database.delete(mySQLiteHelper.TABLE_SONG, mySQLiteHelper.SONG_ID
        + " = " + id, null);
  }

  public List<song> getAllSongs(String inputValue, long last) {
    List<song> songs = new ArrayList<song>();

    Cursor cursor = null;
    String mQuery 	= "select s.songId, s.name, s.artist, s.firstSentence, s.lyric, f.userId, f.favouriteId "; 
    mQuery 			= mQuery + "from song s left join favourite f on f.songId = s.songId and f.userId = ?";// + userId;     
    mQuery = mQuery + " where ";		
    
    try{
	    if(inputValue != "")
	    {
	    	mQuery = mQuery +  mySQLiteHelper.SONG_NAME + " like '%' || ? || '%' and ";//inputValue
	    	mQuery = mQuery + " s." + mySQLiteHelper.SONG_ID + " > ? LIMIT ?";// // last + pageSize;        
	        cursor = database.rawQuery(mQuery, new String [] {String.valueOf(userId), String.valueOf(inputValue), String.valueOf(last), String.valueOf(pageSize)});
	    }
	    else
	    {
	    	mQuery = mQuery + " s." + mySQLiteHelper.SONG_ID + " > ? LIMIT ?";
	        cursor = database.rawQuery(mQuery, new String [] {String.valueOf(userId), String.valueOf(last), String.valueOf(pageSize)});    
	    }
	    
	    cursor.moveToFirst();
	    while (!cursor.isAfterLast()) {
	      song song = cursorToSong(cursor);
	      songs.add(song);
	      cursor.moveToNext();
	    }
	}
	catch(Exception ex)
	{
	}
    finally
    {
    	//Make sure to close the cursor
    	cursor.close();
    }
    return songs;
  }
/*
public List<song> getMoreSongs(String inputValue, long last) {
    List<song> songs = new ArrayList<song>();

    String whereClause = mySQLiteHelper.SONG_ID + " > " + last + " LIMIT " + pageSize; 
    
    if(inputValue != "")
    {
    	whereClause = mySQLiteHelper.SONG_NAME + " like \"%" + inputValue + "%\" and " + whereClause;     	
    }
    
    Cursor cursor = database.query(mySQLiteHelper.TABLE_SONG, songColumns, 
									whereClause,null, null, null, null, null);			
	try{
	    cursor.moveToFirst();
	    while (!cursor.isAfterLast()) {
	      song song = cursorToSong(cursor);
	      songs.add(song);
	      cursor.moveToNext();
	    }
	}
	catch(Exception ex)
	{
	}
	finally
	{
		//Make sure to close the cursor
		cursor.close();
	}
	
	return songs;
}

  
public List<song> searchSongs(String inputValue) {
	List<song> songs = new ArrayList<song>();
	   
	Cursor cursor = database.query(mySQLiteHelper.TABLE_SONG, songColumns, 
					mySQLiteHelper.SONG_NAME + " like \"%" + inputValue + "%\"", null, null, null, null, null);

	try{
	    cursor.moveToFirst();
	    while (!cursor.isAfterLast()) {
	      song song = cursorToSong(cursor);
	      songs.add(song);
	      cursor.moveToNext();
	    }
	}
	catch(Exception ex)
	{
	}
	finally
	{
		//Make sure to close the cursor
		cursor.close();
	}
    
    return songs;
 }
 */ 
  
private song cursorToSong(Cursor cursor) {
    song song = new song();
    song.setSongId(cursor.getLong(0));
    song.setSongName(cursor.getString(1));
    song.setArtist(cursor.getString(2));
    song.setFirstSentence(cursor.getString(3));
    song.setLyric(cursor.getString(4));
    song.setUserId(cursor.getLong(5));
    song.setFavouriteId(cursor.getLong(5));
    return song;
  }
}